# -*- coding: utf-8 -*-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_top_two.py
#日期：2020-11-30
#备注：涨停后调整2天，收盘价不破涨停最低点
CREATE TABLE `stock_top_two` (
  `top_date` varchar(10) NOT NULL COMMENT '涨停日',
  `stock_no` varchar(10) NOT NULL COMMENT '股票代码',
  `stock_name` varchar(10) DEFAULT NULL COMMENT '股票简称',
  `st_price` float DEFAULT '0' COMMENT '涨停日开盘价',
  `top_price` float DEFAULT '0' COMMENT '涨停价',
  `one_price_today` float DEFAULT '0' COMMENT '涨停后第一天开盘价',
  `one_price_new` float DEFAULT '0' COMMENT '涨停后第一天收盘价',
  `one_price_chg` float DEFAULT '0' COMMENT '涨停后第一天涨跌幅',
  `target_date` varchar(32) NOT NULL COMMENT '第2预计交易日早盘低吸',
  `target_price` float DEFAULT '0' COMMENT '低吸目标价',
  -- `is_target_price` float DEFAULT '0' COMMENT '触碰第2预计交易日低吸价格的最低价',
  -- `is_target_date` varchar(32) DEFAULT NULL COMMENT '触碰第2预计交易日低吸价格的日期',
  `target_price_min` float DEFAULT '0' COMMENT '第2预计交易日最低价',
  `target_price_chg` float DEFAULT '0' COMMENT '第2预计交易日涨跌幅',
  -- `two_price_chg` float DEFAULT '0' COMMENT '涨停后第3天涨跌幅',
  -- `three_price_chg` float DEFAULT '0' COMMENT '涨停后第4天涨跌幅',
  -- `three_price_new` float DEFAULT '0' COMMENT '涨停后第4天收盘价',
  `operate` varchar(32) DEFAULT '0' COMMENT '人工处理：0-可以,1-不行',
  `create_time` datetime DEFAULT NULL,
  `operate_result` varchar(32) DEFAULT NULL COMMENT '复盘结果：完美，成功，失败',
  PRIMARY KEY (`stock_no`,`top_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='涨停后调整2天不破涨停实体线股票统计表';

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
from pycacho.cachobase.chinese_workday import get_trade_day
from pacong.stock.stock_config import db,cursor

# T日统计T-1涨停股票,T日成交额小于涨停日成交额1.5倍、T日涨跌幅-8<chg<5、T日最高价不大于开盘价的1.08倍、T日股票活跃度<1000，T日最低价不触碰涨停实体线一半位置、
# T+1日为目标日
def top_stock(top_date,target_date,top_table,one_table):
    sql1 = 'delete from stock_top_two where top_date=%s;' % (top_date)
    print(sql1)
    cursor.execute(sql1)
    db.commit()
    try:
        sql = 'INSERT INTO stock_top_two select a.trade_date,a.stock_no,a.stock_name,a.price_today 涨停日开盘价,a.price_new as 涨停价,b.price_today,b.price_new,b.price_chg,"%s",' \
              'FORMAT(a.price_new-(a.price_new-a.price_today)/2,2) as 低吸目标价,0,0,0,NOW(),"待定" from %s a,%s b where a.stock_no=b.stock_no and b.price_chg<5 and b.price_chg>-8 and a.price_chg>9.8 ' \
              'and b.price_new>(a.price_new-(a.price_new-a.price_today)/2) and b.price_min>a.price_today and b.price_max<b.price_today*1.08 and b.turnover<a.turnover*1.5 and a.stock_no in (select rank_stockNo from stock_rankinfo where rank_num<1000);' %(target_date,top_table,one_table)
        print(sql)
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(str(e))
    print('符合要求股票入库的日期：',top_date)


def get_two_stock(n):
    for i in range(1,n+1):  # N天收盘
        one_table = 'stock_tradeinfo_' + get_trade_day(i-1,'s')
        top_table = 'stock_tradeinfo_' + get_trade_day(i,'s')
        top_stock(get_trade_day(i,'s'),get_trade_day(i-2,'s'),top_table,one_table)

#
def top_is(target_date):
    sql = 'select stock_no as 股票代码,target_date from stock_top_two where target_date="%s";' %(target_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    stocks = []
    for stock in results:
        row =[]
        for info in stock:
            row.append(info)
        stocks.append(row)
    #print(stocks)
    return stocks

# 更新最新价
def top_mysql(td_date,two_date,stock_no,target_date,three_date):
    try:
        sql0 = "update stock_top_two set target_price_min=(select price_min from %s where stock_no='%s'),target_price_chg=(select price_chg from %s where stock_no='%s') " \
               "where stock_no='%s' and target_date='%s';" % (td_date,stock_no,td_date,stock_no, stock_no, target_date)
        print(sql0)
        cursor.execute(sql0)
        db.commit()
    except:
        pass

#
def update_exist_top(i):
    target = top_is(get_trade_day(i, 's'))
    td_date = 'stock_tradeinfo_' + get_trade_day(i, 's')
    two_date = 'stock_tradeinfo_' + get_trade_day(i-1, 's')
    three_date = 'stock_tradeinfo_' +get_trade_day(i-2, 's')
    for stock in target:
        stock_no = stock[0]
        target_date = stock[1]
        print(stock_no,target_date,td_date,two_date,three_date)
        top_mysql(td_date,two_date, stock_no, target_date, three_date)


if __name__ == '__main__':
    n= 2
    get_two_stock(n)
    for i in range(n):           # N-2
        update_exist_top(i)
